<html>
<head>
<title>Tutorial 2 - Creating the loan history report</title>
<link rel="stylesheet" type="text/css" href="../media/style.css">
</head>
<body>

<table border="0" cellspacing="0" cellpadding="0" height="48" width="100%">
  <tr>
    <td width="100%">
			<table width="100%" height="78" border="0" align="left" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" id="topnav">
			<tr>
				<!-- Header Side -->
				<td width="17">
					<img height="78" src="media/reportico100.png"/>
				</td>
				<td align="right"></td>
				<td align="center">
					<table width="100%" height="60" border="0" align="left" cellpadding="0" cellspacing="0">
						<tr>
							<td valign="top" align="left">
							<img src="media/repbanner.png"/></td>
							</td>
						</tr>
						<tr>
							<td align="center" valign="bottom" class="topmenucell">
								<nobr>
								&nbsp;
								</nobr>
							</td>
						</tr>
					</table>
				</td>
				<td width="103" class="toplogincell">
					&nbsp;
				</td>
			</tr>
			</table>
</td>
  </tr>
  <!--tr>
    <td class="header_top">reportico</td>
  </tr-->
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
  <tr>
    <td class="header_menu">
  		  [ <a href="../classtrees_reportico.html" class="menu">class tree: reportico</a> ]
		  [ <a href="../elementindex_reportico.html" class="menu">index: reportico</a> ]
		  [ <a href="../elementindex.html" class="menu">all elements</a> ]
    </td>
  </tr>
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
</table>

<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <tr valign="top">
    <td width="200" class="menu">
      <b>Packages:</b><br />
              <a href="../li_reportico.html">reportico</a><br />
            <br /><br />
		<b>Tutorials/Manuals:</b><br />
					<strong>Package-level:</strong>
							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.pkg.html">Reportico Tutorial And User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.manual.pkg.html">Reportico User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.quickstart.pkg.html">Reportico Installation</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.gettingstarted.pkg.html">Getting Started</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.structure.pkg.html">Reportico Installation Structure</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.using.pkg.html">Configuring Reportico - System Parameters</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.embedding.pkg.html">Embedding Reportico</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.language.pkg.html">Language Support</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.projects.pkg.html">Projects</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.importlink.pkg.html">Importing and linking from reports</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.menus.pkg.html">Report Menus</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.prepare.pkg.html">Report Preparation and Execution</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.design.pkg.html">Report Design Mode</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.format.pkg.html">The Design Format Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.customcode.pkg.html">Custom Source Code</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.qrydet.pkg.html">The Query Details Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.assign.pkg.html">The Assignments Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.criteria.pkg.html">The Criteria Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.output.pkg.html">The Output Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.webservices.pkg.html">Turning Reports into Web Services (currently unavailable)</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.faq.pkg.html">Reportico FAQ</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.howto.pkg.html">Reportico Tutorial</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1.pkg.html">Tutorial No.1 - The Film Listing Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_1.pkg.html">Tutorial 1 Stage 1 - Creating a basic report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_2.pkg.html">Tutorial 1 Stage 2 - Creating User Entry Criteria</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_3.pkg.html">Tutorial 1 Stage 3 - Working with expressions using assignments</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_4.pkg.html">Tutorial 1 Stage 4 - Working with groups</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_5.pkg.html">Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial2_1.pkg.html">Tutorial 2 - Creating the loan history report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial3_1.pkg.html">Tutorial 3 - The Monthly Returns Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial4_1.pkg.html">Tutorial 4 - The Late Returns Summary report</a>
</ul>
</li></ul>
</li></ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix1.pkg.html">Appendix 1 - Reportico Project Configuration parameters</a>
</ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix2.pkg.html">Appendix 2 - Reportico URL request parameters</a>
</ul>

									                        <b>Files:</b><br />
      	  <div class="package">
			<a href="../reportico/_reportico.php.html">		reportico.php
		</a><br>
	  </div><br />
      
                </td>
    <td>
      <table cellpadding="10" cellspacing="0" width="100%" border="0"><tr><td valign="top">

<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10%" align="left" valign="bottom"><a href=
"../reportico/tutorial_reportico.tutorial1_5.pkg.html">Prev</a></td>
<td width="80%" align="center" valign="bottom"></td>
<td width="10%" align="right" valign="bottom"><a href=
"../reportico/tutorial_reportico.tutorial3_1.pkg.html">Next</a></td>
</tr>
</table>
<span><a name="tutorial1."></a><h2 class="title">Tutorial 2 - Creating the loan history report</h2><p><strong class = "bold">Note that this tutorial requires you to enter assignments using PHP code. Assignment code may not be
      entered when running in SAFE mode as the user could create SQL
      statements here that could delete and update data in the
      database or perform disk operations. By default, the tutorial projects have SAFE mode turned on. To continue with this tutorial you need to turn off SAFE mode by folowing the instructions on how to turn on and off Safe Mode in the <a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a> section.</strong></p>
    <p>In this tutorial you will learn how to :-
	<ol><li><p>Add a date range criteria item</p></li>
	<li><p>Add a LIST type criteria item</p></li>
	<li><p>Add a conditional asignment</p></li></ol></p>
   <a name="tut2_1_run"></a><h3 class="title">Beginning the Tutorial 2 Report</h3><p>Select <strong>Loan History - Begin the Tutorial</strong> from the tutorials menu (Alternatively you can point your browser at the URL <strong>http://{SERVER_ADDRESS}/{REPORTICO_INSTALL_DIRECTORY}/run.php?project=tutorials&amp;execute_mode=PREPARE&amp;xmlin=tut2_1_films.xml</strong>). You are now ready to start this tutorial.</p>
   <p>On entry to this tutorial, the SQL data query has already been set up. Press the <strong>Execute</strong>
button to see the output. You should see a history of films borrowed from the DVD library. Press the <strong>Back</strong> button to return and then enter report design mode so that we can set up reporting by date.</p>
   <p>Before configuring this report, it is a good idea to inspect the SQL statement we are using to generate report data. Press the <strong>Query Details</strong> to view the SQL statement. The SQL used in this report looks as follows :-</p>
<p><code><pre>
SELECT member.member_id member_id, member.first_name first_name, 
       member.last_name last_name, film.film_id film_id, film.title title, 
       DATE_FORMAT(loan_date, '%d %M %Y') loaned, 
       DATE_FORMAT(return_date, '%d %M %Y') returned, 
       loan.loan_date loan_date, 
       loan.return_date return_date, 
       join_date join_date 
  FROM reptut_loan loan, reptut_member member, reptut_film film 
 WHERE 1 = 1                                 
   AND loan.member_id = member.member_id
   AND loan.film_id = film.film_id
 ORDER BY member.last_name ASC, member.first_name ASC, 
          loan.loan_date ASC
</pre></code>
This query pulls out a list of all loans made from the DVD library ordered by library member and date. The ordering allows the report to group by member. Notice the use of the standard MySQL DATE_FORMAT function to format the dates into dd/mm/yyyy format before reporting.</p><hr />
   <a name="tut2_1_datecrit"></a><h3 class="title">Set up Date Range Criteria Entry</h3><p>Click on the <strong>Criteria</strong> tab, and then press the <strong>Add</strong> button and press the resulting <strong>Criteria CriteriaName</strong> button. You should be presented with a set of criteria parameters.</p>
   <p>Set these as follows :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Name</td>
  <td>loanDate</td>
  <td>The name of the ecriteria so it can be referred to elsewhere in the report</td></tr>
<tr><td>2.</td>
  <td>Title</td>
  <td>Date Range</td>
  <td>The title of the criteria item as it appears during criteria entry.</td></tr>
<tr><td>3.</td>
  <td>Main Query Column</td>
  <td>&gt;set to blank&lt;</td>
  <td>This option normally indicates what table/column in the main report query corresponds with the criteria item. We are going to link the date range crtieria with SQL within the main report SQL later, so leave this blank</td></tr>
<tr><td>4.</td>
  <td>Criteria Type</td>
  <td>Date Range</td>
  <td>Will ask the report user for a date range when entering crtieria for the report.</td></tr>
<tr><td>5.</td>
  <td>Criteria Display</td>
  <td>No Entry</td>
  <td>With a date range criteria this field is not relevant</td></tr>
<tr><td>6.</td>
  <td>Expand Display</td>
  <td>No Entry</td>
  <td>Indicates that no Expand option is applicable to this criteria item</td></tr>
<tr><td>7.</td>
  <td>Defaults</td>
  <td>FIRSTOFLASTMONTH-TODAY</td>
  <td>The default report period be from the first of last month until today</td></tr></tbody></table></p>
<p>To link this criteria item in with the main query you need to edit the main query. To do this select the <strong>Query Details</strong> option and add the following line after within the WHERE clause - i.e. after the line beginning WHERE but before the line beginnning ORDER :-
<code><pre>
[ AND loan_date between {loanDate,RANGE1} and {loanDate,RANGE2} ]  
</pre></code></p>
<p>This causes the report to replace the values between curly brackets ({}) with the lower and upper dates of your <strong>loanDate</strong> criteria range.</p>
<p>Now test this out by returning to criteria entry mode and by running the report with different radio buttons selected.</p>
<p>Now that your criteria is set up, return to report execution by selecting <strong>Execute</strong> from the drop down list box. You should notice that you criteria is visible and the default dates to run for are from the start of this month until today. Now experiment with the dates by choosing an earlier period and execute the report by pressing the <strong>Execute</strong> button.</p><hr />
   <a name="tut2_1_listcrit"></a><h3 class="title">Setting up a LIST criteria Item</h3><p>Now we are going to set up a criteria item that will allow you to report on all films in the DVD library or just those that are unreturned. The option will in this case be presented as a radio button.</p>
   <p>Return to report design mode and again select the <strong>Criteria</strong> tab, and then press the <strong>Add</strong> button and press the resulting <strong>Criteria CriteriaName</strong> button.</p>
   <p>Set up the criteria options as follows :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Name</td>
  <td>returned</td>
  <td>The name of the criteria so it can be referred to elsewhere in the report</td></tr>
<tr><td>2.</td>
  <td>Title</td>
  <td>Display</td>
  <td>The title of the criteria item as it appears during criteria entry.</td></tr>
<tr><td>3.</td>
  <td>Main Query Column</td>
  <td>&lt;leave blank&gt;</td>
  <td>This option normally indicates what table/column in the main report query corresponds with the criteria item. We are going to link the date range crtieria with SQL within the main report SQL later, so leave this blank</td></tr>
<tr><td>4.</td>
  <td>Criteria Type</td>
  <td>Custom List</td>
  <td>The items the user will select from will be set up later in this criteria item within the <strong>List Values</strong> parameter</td></tr>
<tr><td>5.</td>
  <td>Criteria Display</td>
  <td>Radio Buttons</td>
  <td>This user will select from radio buttons when making critera selection</td></tr>
<tr><td>6.</td>
  <td>Expand Display</td>
  <td>No Entry</td>
  <td>Indicates that no Expand option is applicable to this criteria item</td></tr>
<tr><td>7.</td>
  <td>Defaults</td>
  <td>1</td>
  <td>The default mode to run the report in will be <strong>Report on all films</strong>. The value of 1 equates with &quot;all films&quot; because the next parameter <strong>List Values</strong> dictates this</td></tr>
<tr><td>8.</td>
  <td>List Values</td>
  <td>All=1,Loaned Out=2</td>
  <td>Indicates the the user will be presented with 2 radio buttons labelled &quot;All&quot; and &quot;Loaned Out&quot;. If the user selects &quot;All&quot; then the <strong>returned</strong> criteria item wil be set to 1 otherwise it will be set to 2. This value is then used within the report execution by linking it in with the main query in the next step.</td></tr></tbody></table></p>
<p>To link this criteria item in with the main query you need to edit the main query. To do this select the <strong>Query Details</strong> option and add the following line after within the WHERE clause - i.e. after the line beginning WHERE but before the line beginnning ORDER :-
<code><pre>
[ AND ( return_date IS NULL OR {returned,VALUE} = 1 ) ]  
</pre></code></p>
<p>This causes the report to return all relevant when the value of the <strong>returned</strong> criteria item  is 1 and just  records for unreturned films (return_date is NULL) if it is 2. This sort of lateral thinking makes these LIST criteria items quite powerful.</p>
<p>Now test this out by returning to criteria entry mode and by running the report with different radio buttons selected.</p><hr />
   <a name="tut2_1_"></a><h3 class="title">Setting up a conditional assignment</h3><p>Now we are going to set up a conditional assinment that will cause the report to display the text &quot;**UNRETURNED**&quot; instead of the return date against films that have not been returned.</p>
   <p>To do this enter report design mode and select the <strong>Assignments</strong> tab. Then add a new Assignment and click the resulting <strong>Assignment Column</strong> button. Now set the following parameters for the new assignment :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To</td>
  <td>returned</td>
  <td>This column normally represents the return date of a film. It is this column that we are resetting if its value is blank - i.e. the film has not been returned.</td></tr>
<tr><td>2.</td>
  <td>Expression</td>
  <td>&quot;**UNRETURNED**&quot;</td>
  <td>This is the text we are going to change the <strong>returned</strong> column to. Note that you must include the quotes around the text value for this to work since the value must be an expression valid within the PHP language.</td></tr>
<tr><td>3.</td>
  <td>Condition</td>
  <td>!{returned}</td>
  <td>Indicates that this assignment will only be made if the value of  the <strong>returned</strong> column is false - or in other words NULL.</td></tr></tbody></table></p>
<p>Finally test your new assignment by running the report for a period that includes unreturned films.</p><hr />
   <a name="tut2_1_2"></a><h3 class="title">Setting text colour based on column value</h3><p>Now we are going to make the return date go red if it is set to &quot;**UNRETURNED**&quot;</p>
   <p>To do this enter report design mode and select the <strong>Assignments</strong> tab. Then add a new Assignment and click the resulting <strong>Assignment Column</strong> button. Now click on the <strong>Output Styles</strong> Wizard and set the following parameters for the new assignment :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To</td>
  <td>returned</td>
  <td>This is the column we wish to turn red</td></tr>
<tr><td>2.</td>
  <td>Expression</td>
  <td></td>
  <td>Leave blank</td></tr>
<tr><td>3.</td>
  <td>Condition</td>
  <td>{returned} == &quot;**UNRETURNED**&quot;</td>
  <td>Since we set the value to **UNRETURNED** in the previous section, this is the value we wish to test for to turn it red</td></tr>
<tr><td>4.</td>
  <td>Text Colour</td>
  <td>#ff0000</td>
  <td>HTML text colour #ff0000 is red</td></tr></tbody></table></p>
<p>Finally test your new assignment by running the report for a period that includes unreturned films. Run in PDF mode too to see the colour change!</p><hr />
   <a name="tut2_1_3"></a><h3 class="title">Set your rows to appear in alternating colours</h3><p>We are going to create two assignments, one to set a default row background colour and then another to use a different colour on every other row.</p>
   <p>To do this enter report design mode and select the <strong>Assignments</strong> tab. Then add a new Assignment and click the resulting <strong>Assignment Column</strong> button. Now click on the <strong>Output Styles</strong> Wizard and set the following parameters for the new assignment :-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To</td>
  <td>member_id</td>
  <td>This can be any column since this assignment relates to the row as a whole</td></tr>
<tr><td>2.</td>
  <td>Apply Style To</td>
  <td>Row</td>
  <td>It is the row that we want to apply the background to</td></tr>
<tr><td>3.</td>
  <td>Style Background Colour</td>
  <td>#f3f3ff</td>
  <td>A very light blue</td></tr></tbody></table></p>
   <p>After pressing ok, add another assignment  but this time setting a different colour and with a condition as detailed below.
So add another assignment and again click on the <strong>Output Styles</strong> Wizard and set the following parameters:-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Comment</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To</td>
  <td>member_id</td>
  <td>This can be any column since this assignment relates to the row as a whole</td></tr>
<tr><td>2.</td>
  <td>Condition</td>
  <td>lineno() % 2 == 1</td>
  <td>Will apply to every other line as this evaluates to true when the remainder of the line nu,ber divided by 2 = 1</td></tr>
<tr><td>3.</td>
  <td>Apply Style To</td>
  <td>Row</td>
  <td>Applies background to report output rows</td></tr>
<tr><td>4.</td>
  <td>Style Background Colour</td>
  <td>#ddddff</td>
  <td>A slightly darker blue</td></tr></tbody></table></p>
<p>Finally test your new assignment by running the report for a period that includes unreturned films. Run in PDF mode too to see the colour change!</p><hr /></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="33%" align="left" valign="top"><a href="../reportico/tutorial_reportico.tutorial1_5.pkg.html">Prev</a></td>
<td width="34%" align="center" valign="top"><a href=
"../reportico/tutorial_reportico.howto.pkg.html">Up</a></td>
<td width="33%" align="right" valign="top"><a href=
"../reportico/tutorial_reportico.tutorial3_1.pkg.html">Next</a></td>
</tr>

<tr>
<td width="33%" align="left" valign="top">Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns</td>
<td width="34%" align="center" valign="top">Reportico Tutorial</td>
<td width="33%" align="right" valign="top">Tutorial 3 - The Monthly Returns Report</td>
</tr>
</table>
        <div class="credit">
		    <hr />
		    Documentation generated on Mon, 09 Jun 2014 18:10:08 +0100 by <a href="http://www.phpdoc.org">phpDocumentor 1.4.3</a>
	      </div>
      </td></tr></table>
    </td>
  </tr>
</table>

</body>
</html>